package com.yue.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelToSQLGenerator {

    public static void main(String[] args) {
        //读取你的Excel文件路径
        String excelFilePath = "D:\\Program Files\\JetBrains\\excel_util\\2024数据生成.xlsx";

        //生成SQL文件的路径
        String outputSQLFilePath = "D:\\Program Files\\JetBrains\\excel_util\\generator_sql.sql";

        List<String> sqlStatements = new ArrayList<>();

        try (FileInputStream inputStream = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheet("Sheet1"); // 获取名为“Sheet1”的工作表

            // 行
            for (Row row : sheet) {
                // 跳过表头行，假设第一行是表头
                if (row.getRowNum() == 0) {
                    continue;
                }

                // 获取产品代码、时效和是否列的数据
                Cell productCodeCell = row.getCell(0); // 第一列为产品代码
                Cell ageingCell = row.getCell(1); // 第二列为时效
                Cell yesOrNoCell = row.getCell(2); // 第三列为是否

                if (productCodeCell != null && ageingCell != null && yesOrNoCell != null) {
                    String productCode = getStringValue(productCodeCell);
                    String ageing = getStringValue(ageingCell);
                    String yesOrNo = getStringValue(yesOrNoCell);

                    // 截取时效T+N的最后一个字符
                    if (ageing.length() > 0) {
                        ageing = ageing.substring(ageing.length() - 1);
                    }

                    // 处理是否列：男转N，女转Y，空不处理
                    if ("否".equals(yesOrNo)) {
                        yesOrNo = "N";
                    } else if ("是".equals(yesOrNo)) {
                        yesOrNo = "Y";
                    }

                    // 生成update语句并添加到列表中
                    String sql = "UPDATE your_table SET ageing = '" + ageing + "', yesOrNo = '" + yesOrNo + "' WHERE product_code = '" + productCode + "';";
                    sqlStatements.add(sql);
                }
            }

            // 将生成的SQL语句写入到输出文件
            writeSQLToFile(sqlStatements, outputSQLFilePath);

            // 输出成功信息
            System.out.println("SQL文件生成成功" + outputSQLFilePath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取单元格的字符串值，转换处理
     * @param cell
     * @return
     */
    private static String getStringValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue(); // 直接返回字符串值
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 如果是日期格式，你可能希望以特定格式返回日期字符串
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(cell.getDateCellValue());
                } else {
                    // 整数型，返回整数值的字符串表示
                    return String.valueOf((int) cell.getNumericCellValue());
                }
            default:
                return ""; // 其他类型的单元格返回空字符串
        }

    }

    /**
     * 将SQL语句写入到文件
     * @param sqlStatements
     * @param filePath
     * @throws IOException
     */
    private static void writeSQLToFile(List<String> sqlStatements, String filePath) throws IOException {
        try (OutputStream outputStream = new FileOutputStream(filePath)) {
            for (String sql : sqlStatements) {
                outputStream.write((sql + "\n").getBytes());
            }
        }
    }
}